package com.ruoyi.dm.component.manager;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.dm.domain.BO.DenDataBO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * 密度保存
 * 根据上传的卫星,自动建表,并存数据
 */
@Slf4j
@Component
public class DensityDataManager {


    @Autowired
    JdbcTemplate jdbcTemplate;

    //序列
    //private String sequenceSql ="create sequence %s_seq start with 1 increment by 1";

    //监测
    private String checkSql ="select 1 from %s limit 1";

    //建索引
    private String indexSql ="CREATE INDEX %s_TIME ON PIESAT_DGDQ.%s (TIME)";

    //dm 数据库
    //private String creatDbSql = "CREATE TABLE %s ( ID BIGINT DEFAULT %s_seq.nextval NOT NULL, LAT DOUBLE,LON DOUBLE, ALT DOUBLE,TIME timestamp,DENSITY DECIMAL,PRIMARY KEY (ID))" +
    //        "PARTITION BY RANGE (TIME) ("+
    //        "PARTITION P1 VALUES LESS THAN ('2020-01-01')," +
    //        "PARTITION P2 VALUES LESS THAN ('2022-01-01')," +
    //        "PARTITION P3 VALUES LESS THAN ('2024-01-01')," +
    //        "PARTITION P4 VALUES LESS THAN ('2026-01-01')," +
    //        "PARTITION P5 VALUES LESS THAN ('2028-01-01')," +
    //        "PARTITION P6 VALUES LESS THAN ('2030-01-01')," +
    //        "PARTITION P7 VALUES LESS THAN ('2032-01-01')," +
    //        "PARTITION P8 VALUES LESS THAN ('2034-01-01')," +
    //        "PARTITION P9 VALUES LESS THAN ('2036-01-01')," +
    //        "PARTITION P10 VALUES LESS THAN ('2100-01-01') )";

    // gbase数据库
    private String creatDbSql = "CREATE TABLE %s ( ID SERIAL PRIMARY KEY , LAT DECIMAL,LON DECIMAL, ALT DECIMAL,TIME timestamp,DENSITY DECIMAL)" +
            "PARTITION BY RANGE (TIME) ("+
            "PARTITION P1 VALUES LESS THAN ('2000-01-01')," +
            "PARTITION P2 VALUES LESS THAN ('2002-01-01')," +
            "PARTITION P3 VALUES LESS THAN ('2004-01-01')," +
            "PARTITION P4 VALUES LESS THAN ('2006-01-01')," +
            "PARTITION P5 VALUES LESS THAN ('2008-01-01')," +
            "PARTITION P6 VALUES LESS THAN ('2010-01-01')," +
            "PARTITION P7 VALUES LESS THAN ('2012-01-01')," +
            "PARTITION P8 VALUES LESS THAN ('2014-01-01')," +
            "PARTITION P9 VALUES LESS THAN ('2016-01-01')," +
            "PARTITION P10 VALUES LESS THAN ('2018-01-01')," +
            "PARTITION P11 VALUES LESS THAN ('2020-01-01')," +
            "PARTITION P12 VALUES LESS THAN ('2022-01-01')," +
            "PARTITION P13 VALUES LESS THAN ('2024-01-01')," +
            "PARTITION P14 VALUES LESS THAN ('2026-01-01')," +
            "PARTITION P15 VALUES LESS THAN ('2028-01-01')," +
            "PARTITION P16 VALUES LESS THAN ('2030-01-01')," +
            "PARTITION P17 VALUES LESS THAN ('2032-01-01')," +
            "PARTITION P18 VALUES LESS THAN ('2034-01-01')," +
            "PARTITION P19 VALUES LESS THAN ('2036-01-01')," +
            "PARTITION P20 VALUES LESS THAN ('2100-01-01') )";

    //备注
    private String commont1  = "comment on column %s.LAT  IS '纬度'";
    private String commont2  = "comment on column %s.LON  IS '经度'";
    private String commont3  = "comment on column %s.ALT  IS '高度'";
    private String commont4  = "comment on column %s.TIME  IS '时间'";
    private String commont5  = "comment on column %s.DENSITY  IS '质量'";

    private String commont6  = "comment on table %s  IS '质量观测数据'";

    private String columnSql = "insert into %s (LAT,LON,ALT,TIME,DENSITY) values";
    private String valueSql = "('%s','%s','%s','%s','%s')";

    /**
     * 读取文件
     * @param satellite
     * @param file
     */
    public void saveDensityData(String satellite, String file) throws IOException {
        BufferedReader acc = new BufferedReader(new FileReader(file));
        //首行是表头
        //Time(UTC),  Altitude(m),  Geodetic LON(degree),     Geodetic LAT(degree),  Density(kg/m3)
        //2021-09-06 12:15:19     392896.0     -96.188    -40.421    9.34e-13
        acc.readLine();
        String line = "";
        List<DenDataBO> list = new ArrayList<>(1000);
        while (null != (line=acc.readLine())) {
            String[] split = line.split("\\s{2,}");
            DenDataBO build = DenDataBO.builder().time(split[0]).alt(split[1]).lon(split[2]).lat(split[3]).density(split[4]).build();
            list.add(build);
            if (list.size() >= 1000) {
                saveData(satellite, list);
                list = new ArrayList<>(1000);
            }
        }
        saveData(satellite, list);
    }




    /**
     * 读取apod星数据,拼接时间
     * @param satellite
     * @param file
     */
    public void saveApodData(String satellite, String file) throws IOException {
        BufferedReader acc = new BufferedReader(new FileReader(file));
        try {
            //ato_20190103.dat
            // UTC,纬度,经度,高度,地方时,探测密度,MSISE密度  58849
            // 58484.0000810     70.1854    281.3566    445.8746 18.7590    .2573443374574365E-12    .1818730880196509E-12
            String line = "";
            List<DenDataBO> list = new ArrayList<>(10000);
            BigDecimal totalSecond = new BigDecimal(86400);
            DateTime start = DateUtil.parse("18581117", "yyyyMMdd");
            Set<String> timeSet = new HashSet<>();

            int count = 0;
            while (null != (line = acc.readLine())) {
                count++;
                String[] split = line.split("\\s+");
                String timeStr = split[1];
                if (timeSet.contains(timeStr)) {
                    log.info("剔除重复时间:{}", timeStr);
                    continue;
                } else {
                    timeSet.add(timeStr);
                }
                BigDecimal bigDecimal = new BigDecimal(split[1]);
                //整数部分是天
                int day = bigDecimal.intValue();
                //小数部分是秒
                BigDecimal multiply = bigDecimal.subtract(new BigDecimal(day)).multiply(totalSecond).setScale(0, BigDecimal.ROUND_HALF_UP);
                int second = multiply.intValue();

                DateTime begin = DateUtil.offsetDay(start, day);
                DateTime dateTime = DateUtil.offsetSecond(begin, second);
                BigDecimal altDecimal = new BigDecimal(split[4]);
                altDecimal = altDecimal.multiply(BigDecimal.valueOf(1000));
                DenDataBO build = DenDataBO.builder().time(dateTime.toString()).alt(altDecimal.toPlainString()).lon(split[3]).lat(split[2]).density(split[6]).build();
                list.add(build);
                if (list.size() >= 10000) {
                    saveData(satellite, list);
                    list = new ArrayList<>(10000);
                }

            }
            saveData(satellite, list);
            log.info("{} size:{}", file, count);
        }finally {
            acc.close();
        }
    }



    /**
     * 保存卫星数据
     * @param satellite
     * @param list
     */
    private synchronized void saveData(String satellite, List<DenDataBO> list) {

        if (!StringUtils.hasLength(satellite)) {
            throw new ServiceException("卫星名字不得为空");
        }

        String tableName = "SWDATA_OBS_DEN_"+satellite.toUpperCase();

        dbCheck(tableName);

        List<List<DenDataBO>> split = CollectionUtil.split(list, 10000);
        for (List<DenDataBO> part : split) {
            String insert = String.format(columnSql, tableName);
            if (part.size()==1) {
                DenDataBO bo = part.get(0);
                jdbcTemplate.execute(insert +String.format(valueSql,bo.getLat(),bo.getLon(),bo.getAlt(),bo.getTime(),bo.getDensity()));
            }else {
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append(insert);
                for (DenDataBO bo : part) {
                    stringBuffer.append(String.format(valueSql,bo.getLat(),bo.getLon(),bo.getAlt(),bo.getTime(),bo.getDensity()));
                    stringBuffer.append(",");
                }
                //去掉最后的逗号
                stringBuffer.deleteCharAt(stringBuffer.length()-1);
                jdbcTemplate.execute(stringBuffer.toString());
            }
        }
        //removeReatData(tableName);
    }

    //除重
    private void removeReatData(String tableName) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("DELETE FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" WHERE id IN ( SELECT min(ta.id) FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" ta WHERE time IN  (");
        stringBuffer.append("SELECT tt FROM (");
        stringBuffer.append("SELECT count(1) AS num,\"TIME\" AS tt FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" GROUP BY \"TIME\" ) WHERE num >1 ) GROUP BY ta.\"TIME\" )");
        jdbcTemplate.execute(stringBuffer.toString());
    }



    /**
     * 监测是否需要创建表,若需要则创建
     * @param tableName
     */
    public  void dbCheck(String tableName) {
        try {
            jdbcTemplate.execute(String.format(checkSql,tableName));
            log.info("监测表已存在:{}",tableName);
        }catch (BadSqlGrammarException e) {
            try {
                log.info("创建表:{}",tableName);
                createDb(tableName);
            }catch (Exception ee) {
                log.error("",ee);
                throw new ServiceException("创建表异常");
            }
            //if (cause instanceof DMException) {
            //    DMException dm = (DMException) cause;
            //    if (-2106 == dm.getErrorCode()) {
            //        //无效的表或视图 去创建
            //        log.info("监测表不存在,去创建:{}",tableName);
            //        createDb(tableName);
            //    }else {
            //        throw new ServiceException("创建表异常");
            //    }
            //}
        }catch (Exception e) {
            log.error("",e);
            throw new ServiceException("表异常");
        }

    }

    /**
     * 创建序列,建表,索引
     * @param tableName
     */
    private void createDb(String tableName) {
        //try {
        //    jdbcTemplate.execute(String.format(sequenceSql, tableName, tableName));
        //}catch (Exception e) {
        //    log.info("序列已存在");
        //}
        jdbcTemplate.execute(String.format(creatDbSql,tableName,tableName));
        jdbcTemplate.execute(String.format(commont1,tableName));
        jdbcTemplate.execute(String.format(commont2,tableName));
        jdbcTemplate.execute(String.format(commont3,tableName));
        jdbcTemplate.execute(String.format(commont4,tableName));
        jdbcTemplate.execute(String.format(commont5,tableName));
        jdbcTemplate.execute(String.format(commont6,tableName));
        try {
            jdbcTemplate.execute(String.format(indexSql, tableName, tableName));
        }catch (Exception e) {
            log.info("索引已存在");
        }
    }


}
